Java Database Programming with JDBC Java Database Programming with JDBC
by Pratik Patel
Coriolis, The Coriolis Group
ISBN: 1576100561   Pub Date: 10/01/96
  

Previous Table of Contents Next


Chapter 7
Working With Query Results

So far, we’ve been concentrating on how to use the classes in the JDBC to perform SQL queries. That’s great, but now we have to do something with the data we’ve retrieved. The end user of your JDBC applets or applications will want to see more than just rows and rows of data. In this chapter, we’ll learn how to package the raw table data that is returned after a successful SQL query into a Java object, and then how to use this packaged data to produce easy-to-read graphs.

The first issue we’ll look at is creating a Java object to store the results of a query. This object will provide a usable interface to the actual query results so they can be plugged into a Java graphics library. We’ll create a simple data structure to hold the column results in a formatted way so that we can easily parse them and prepare them for display. Second, we’ll look at taking these results in the Java object and setting up the necessary code to plug the data into a pie chart and bar graph Java package.

In the next chapter, we’ll go one step further and work with BLOB data types (like images). Between these chapters, I will be providing plenty of examples, complete with code, to help you work up your own JDBC programs. At the very least, these chapters will give you some ideas for dealing with raw table data and displaying it in an effective manner.

A Basic Java Object For Storing Results

Although the JDBC provides you with the ResultSet class to get the data from an SQL query, you will still need to store and format within your program the results for display. The smart way to do this is in a re-usable fashion (by implementing a generic object or class) which allows you to re-use the same class you develop to retrieve data from a query in any of your JDBC programs. The code snippet in Listing 7.1 is a method that will keep your results in a Java object until you are ready to parse and display it.

Let’s begin by defining the data we will be getting from the source, and determining how we want to structure it within our Java applet. Remember that the ResultSet allows us to retrieve data in a row-by-row, column-by-column fashion; it simply gives us sequential access to the resulting table data. Table 7.1 shows the example table we will be using in this chapter.

Table 7.1 Example table.
emp_no first_name last_name salary
01234 Pratik Patel 8000
1235 Karl Moss 23000
0002 Keith Weiskamp 90000
0045 Ron Pronk 59999
0067 David Friedel 53000

The optimal way to store this data in our Java program is to put each column’s data in its own structure and then link the different columns by using an index; this will allow us to keep the columnar relationship of the table intact. We will put each column’s data in an array. To simplify matters, we’ll use the getString method, which translates the different data types returned by a query into a String type. Then, we’ll take the data in a column and delimit the instances with commas. We’ll use an array of String to do this; each place in the array will represent a different column. The data object we will create is shown here:

table_data[0] => 01234,1235,0002,0045,0067
table_data[1] => Pratik,Karl,Keith,Ron,David
table_data[2] => Patel,Moss,Weiskamp,Pronk,Friedel
table_data[3] => 8000,23000,90000,59999,53000

Listing 7.1 shows the method we’ll use to query the database and return a String array that contains the resulting table data.

Listing 7.1 The getData method.

public String[] getData( String QueryLine ) {
// Run the QueryLine SQL query, and return the resulting columns in an
// array of String. The first column is at index [0], the second at [1], // etc.

  int columns, pos;
  String column[]=new String[4];
// We have to initialize the column String variable even though we re-
// declare it below. The reason is because the declaration below is in a
// try{} statement, and the compiler will complain that the variable may
// not be initialized.

  boolean more;

 try {

      Statement stmt = con.createStatement();
        // Create a Statement object from the
        // Connection.createStatement method.

      ResultSet rs = stmt.executeQuery(QueryLine);
        // Execute the passed in query, and get
        // the ResultSet for the query.

      columns=(rs.getMetaData()).getColumnCount();
        // Get the number of columns in the resulting table so we can
        // declare the column String array, and so we can loop
        // through the results and retrieve them.

      column = new String[columns];
        // Create the column variable to be the exact number of
        // columns that are in the result table.
        // Initialize the column array to be blank since we'll be adding
        // directly to them later.

for(pos=1; pos<=columns; pos++) {
        column[pos-1]="";
      }

      more=rs.next();
        // Get the first row of the ResultSet. Loop through the ResultSet
        // and get the data, row-by-row, column-by-column.
      while(more) {

        for (pos=1; pos<=columns; pos++) {
          column[pos-1]+=(rs.getString(pos));
            // Add each column to the respective column[] String array.
        }

        more=rs.next();
          // Get the next row of the result if it exists.

          // Now add a comma to each array element to delimit this row is
          // done.
        for (pos=1; pos<=columns; pos++) {
          if(more) {
          // We only want to do this if this isn't the last row of the
          // table!
            column[pos-1]+=(",");
          }
        }
      }
      stmt.close();
        // All done. Close the statement object.
    }
    catch( Exception e ) {
      e.printStackTrace();
      System.out.println(e.getMessage());
    }
return column;
// Finally, return the entire column[] array.
}

Showing The Results

Now that we have the data nicely packaged into our Java object, how do we show it? The code in Listing 7.2 dumps the data in the object to the screen. We simply loop through the array and print the data.

Listing 7.2 Code to print retrieved data to the console.

public void ShowFormattedData(String[] columnD ) {

int i;

for ( i=0; i< columnD.length; i++) {
      System.out.println(columnD[i]+"\n");
  }
}


Previous Table of Contents Next